package com.jsmtr.rms.service.impl;

import java.io.IOException;
import java.io.PrintWriter;
import java.util.*;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.jsmtr.rms.entity.vo.PassStateStatisticVo;
import com.sun.istack.NotNull;
import com.sun.istack.Nullable;
import org.springframework.stereotype.Service;

import com.jsmtr.rms.dao.DaoService;
import com.jsmtr.rms.entity.*;
import com.jsmtr.rms.service.IUtilService;
import com.jsmtr.rms.utils.EnumTools;
import com.jsmtr.rms.utils.RightInterceptor;
import com.jsmtr.rms.utils.StringTools;
import org.springframework.transaction.annotation.Transactional;

@Service
@Transactional
public class UtilService implements IUtilService {

    @Resource
    private DaoService dao;

    public void setDao(DaoService dao) {
        this.dao = dao;
    }

    @Override
    public void printJsonResult(boolean state, String Message, String url,
                                HttpServletResponse response) {
        String result = "{\"state\":" + state + ",\"Message\":\"" + Message + "\",\"url\":\"" + url + "\"}";
        PrintWriter out = null;
        response.setContentType("text/json");
        try {
            out = response.getWriter();
            out.write(result);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            out.flush();
            out.close();
        }

    }

    @Override
    public void printJsonResult(String json, HttpServletResponse response) {
        PrintWriter out = null;
        response.setContentType("application/json");
        try {
            out = response.getWriter();
            out.write(json);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            out.flush();
            out.close();
        }

    }

    @Override
    public void printTextResult(String result, HttpServletResponse response) {
        PrintWriter out = null;
        response.setContentType("text/plain");
        try {
            out = response.getWriter();
            out.write(result);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            out.flush();
            out.close();
        }
    }

    @Override
    public BaseUser getSessionUser(HttpServletRequest request) {
        return (BaseUser) request.getSession().getAttribute(RightInterceptor.SEESION_MEMBER);
    }

    @Override
    public NZResume getNZUser(HttpServletRequest request) {
        return (NZResume) request.getSession().getAttribute(RightInterceptor.SEESION_NZUSER);
    }

    @Override
    public BaseEmployee getEmployeeUser(HttpServletRequest request) {
        return (BaseEmployee) request.getSession().getAttribute("employee");
    }


    @Override
    public void addObject(Object obj) throws Exception {
        dao.add(obj);
    }

    @SuppressWarnings("rawtypes")
    @Override
    @Transactional
    public void addList(List list) throws Exception {
        dao.addAll(list);
    }

    @SuppressWarnings("rawtypes")
    @Override
    @Transactional
    public void addList(List dellist, List addlist) throws Exception {
        dao.deleteAll(dellist);
        dao.addAll(addlist);
    }

    @Override
    @Transactional
    public void editObject(Object obj) throws Exception {
        dao.update(obj);
    }

    @Override
    @Transactional
    public void editObject(Object editobj, Object addobj) throws Exception {
        dao.update(editobj);
        dao.add(addobj);
    }

    @SuppressWarnings("rawtypes")
    @Override
    @Transactional
    public void editObject(Object editobj, List addlist) throws Exception {
        dao.update(editobj);
        dao.addAll(addlist);
    }

    @SuppressWarnings("rawtypes")
    @Override
    @Transactional
    public void editList(List list) throws Exception {
        dao.updateAll(list);
    }

    @SuppressWarnings("rawtypes")
    @Override
    @Transactional
    public void editList(List editlist, Object addobj) throws Exception {
        dao.updateAll(editlist);
        dao.add(addobj);
    }

    @SuppressWarnings("rawtypes")
    @Override
    @Transactional
    public void editList(List editlist, List addlist) throws Exception {
        dao.updateAll(editlist);
        dao.addAll(addlist);
    }

    @SuppressWarnings("rawtypes")
    @Override
    @Transactional
    public void editList(Object editobj, List dellist, List addlist) throws Exception {
        dao.update(editobj);
        dao.deleteAll(dellist);
        dao.addAll(addlist);
    }

    @SuppressWarnings("rawtypes")
    @Override
    @Transactional
    public void editList(List dellist, List editlist, List addlist) throws Exception {
        dao.deleteAll(dellist);
        dao.updateAll(editlist);
        dao.addAll(addlist);
    }

    @Override
    @Transactional
    public int editSqls(List<String> sqls) throws Exception {
        return dao.executeSQLs(sqls);
    }

    @Override
    @Transactional
    public void delObject(Object delobj) throws Exception {
        dao.delete(delobj);
    }

    @SuppressWarnings("rawtypes")
    @Override
    @Transactional
    public void delList(List delList) throws Exception {
        dao.deleteAll(delList);
    }

    @SuppressWarnings("rawtypes")
    @Override
    @Transactional
    public void delList(List delList, List editlist) throws Exception {
        dao.deleteAll(delList);
        dao.updateAll(editlist);
    }

    @SuppressWarnings("rawtypes")
    @Override
    public void delList(Object delobj, List editlist) throws Exception {
        dao.delete(delobj);
        dao.updateAll(editlist);
    }

    @Override
    @Transactional
    public void saveLog(SystemLog log) throws Exception {
        dao.saveLog(log);
    }

    @Override
    @Transactional(readOnly = true)
    public String getConfig(String code) throws Exception {
        BaseConfig config = (BaseConfig) dao.findUnique("from BaseConfig where code='" + code + "'");
        return config.getCont();
    }

    @Override
    @Transactional(readOnly = true)
    public BaseConfig findConfig(String code) throws Exception {
        BaseConfig config = (BaseConfig) dao.findUnique("from BaseConfig where code='" + code + "'");
        return config;
    }

    @SuppressWarnings("unchecked")
    @Override
    @Transactional(readOnly = true)
    public List<BaseConfig> getConfigs(String groupid) throws Exception {
        return dao.find("from BaseConfig t where t.groupid='" + groupid + "'");
    }
	
	/*@Override
	public DoFlowInfo addFlowInfo(DUserResume resume,PlanInfoFlow flow,int result) throws Exception{
		DoFlowInfo flowInfo = new DoFlowInfo();
		flowInfo.setAddtime(new Date());
		flowInfo.setFid(flow.getId());
		flowInfo.setForder(flow.getForder());
		flowInfo.setId(UUID.randomUUID().toString());
		flowInfo.setNickname(flow.getNickname());
		flowInfo.setPid(resume.getPid());
		flowInfo.setPname(resume.getPname());
		flowInfo.setJid(resume.getJid());
		flowInfo.setJname(resume.getJname());
		flowInfo.setResult(result);
		flowInfo.setRid(resume.getId());
		flowInfo.setUserid(resume.getUserid());
		
		return flowInfo;
	}*/
	
	/*@Override
	public DoFlowInfo addFlowInfo(DoExamUser user,PlanInfoFlow flow) throws Exception{
		DoFlowInfo flowInfo = new DoFlowInfo();
		flowInfo.setAddtime(new Date());
		flowInfo.setFid(flow.getId());
		flowInfo.setForder(flow.getForder());
		flowInfo.setId(UUID.randomUUID().toString());
		flowInfo.setNickname(flow.getNickname());
		flowInfo.setPid(user.getZpid());
		flowInfo.setPname(user.getZpname());
		flowInfo.setJid(user.getJid());
		flowInfo.setJname(user.getJname());
		flowInfo.setResult(user.getState());
		flowInfo.setRid(user.getRid());
		flowInfo.setUserid(user.getRuserid());
		
		return flowInfo;
	}*/
	
	/*@Override
	public DoFlowInfo addFlowInfo(DoInterviewUser user,PlanInfoFlow flow) throws Exception{
		DoFlowInfo flowInfo = new DoFlowInfo();
		flowInfo.setAddtime(new Date());
		flowInfo.setFid(flow.getId());
		flowInfo.setForder(flow.getForder());
		flowInfo.setId(UUID.randomUUID().toString());
		flowInfo.setNickname(flow.getNickname());
		flowInfo.setPid(user.getZpid());
		flowInfo.setPname(user.getZpname());
		flowInfo.setJid(user.getJid());
		flowInfo.setJname(user.getJname());
		flowInfo.setResult(user.getState());
		flowInfo.setRid(user.getRid());
		flowInfo.setUserid(user.getRuserid());
		
		return flowInfo;
	}*/
	
	/*@Override
	public DoFlowInfo addFlowInfo(DoPoliticalReviewUser user,PlanInfoFlow flow) throws Exception{
		DoFlowInfo flowInfo = new DoFlowInfo();
		flowInfo.setAddtime(new Date());
		flowInfo.setFid(flow.getId());
		flowInfo.setForder(flow.getForder());
		flowInfo.setId(UUID.randomUUID().toString());
		flowInfo.setNickname(flow.getNickname());
		flowInfo.setPid(user.getZpid());
		flowInfo.setPname(user.getZpname());
		flowInfo.setJid(user.getJid());
		flowInfo.setJname(user.getJname());
		flowInfo.setResult(user.getState());
		flowInfo.setRid(user.getRid());
		flowInfo.setUserid(user.getRuserid());
		
		return flowInfo;
	}*/
	
	/*@Override
	public DoFlowInfo addFlowInfo(DoPhysicalUser user,PlanInfoFlow flow) throws Exception{
		DoFlowInfo flowInfo = new DoFlowInfo();
		flowInfo.setAddtime(new Date());
		flowInfo.setFid(flow.getId());
		flowInfo.setForder(flow.getForder());
		flowInfo.setId(UUID.randomUUID().toString());
		flowInfo.setNickname(flow.getNickname());
		flowInfo.setPid(user.getZpid());
		flowInfo.setPname(user.getZpname());
		flowInfo.setJid(user.getJid());
		flowInfo.setJname(user.getJname());
		flowInfo.setResult(user.getState());
		flowInfo.setRid(user.getRid());
		flowInfo.setUserid(user.getRuserid());
		
		return flowInfo;
	}*/
	
	/*@Override
	public DoFlowInfo addFlowInfo(DoEmploymentUser user,PlanInfoFlow flow) throws Exception{
		DoFlowInfo flowInfo = new DoFlowInfo();
		flowInfo.setAddtime(new Date());
		flowInfo.setFid(flow.getId());
		flowInfo.setForder(flow.getForder());
		flowInfo.setId(UUID.randomUUID().toString());
		flowInfo.setNickname(flow.getNickname());
		flowInfo.setPid(user.getZpid());
		flowInfo.setPname(user.getZpname());
		flowInfo.setJid(user.getJid());
		flowInfo.setJname(user.getJname());
		flowInfo.setResult(user.getState());
		flowInfo.setRid(user.getRid());
		flowInfo.setUserid(user.getRuserid());
		
		return flowInfo;
	}*/

    @Override
    @Transactional(readOnly = true)
    public ProjectInfoFlow getFlow(String id) throws Exception {
        return (ProjectInfoFlow) dao.findUnique("from ProjectInfoFlow where id='" + id + "'");
    }
	
	/*@Override
	public PlanInfoFlow getPrevFlow(String pid) throws Exception{
		return (PlanInfoFlow)dao.findFirst("from PlanInfoFlow where pid='"+pid+"' and state='"+EnumTools.PlanFlowState.已完成.ordinal()+"' order by forder desc");
	}*/

    @Override
    @Transactional(readOnly = true)
    public ProjectInfoFlow getNextFlow(String pid, String deptcode) throws Exception {
        return (ProjectInfoFlow) dao.findFirst("from ProjectInfoFlow where pid='" + pid + "' and deptcode='" + deptcode + "' and state='" + EnumTools.PlanFlowState.未开始.ordinal() + "' order by forder");
    }

    @SuppressWarnings("unchecked")
    @Override
    @Transactional(readOnly = true)
    public Map<String, Object> getExamUsers(int page, int perpage, String epid) throws Exception {
        Map<String, Object> map = new HashMap<String, Object>();
        StringBuffer queryHQL = new StringBuffer("select r from DUserResume r,DoExamUser u where r.id=u.rid ");
        StringBuffer countHQL = new StringBuffer("select count(*) from DUserResume r,DoExamUser u where r.id=u.rid  ");
        StringBuffer condition = new StringBuffer();

        if (StringTools.trimNotEmpty(epid)) {
            condition.append("and u.pid = '" + epid + "' ");
        }
        condition.append("order by r.code");

        queryHQL.append(condition);
        countHQL.append(condition);
        double count = ((Long) dao.findUnique(countHQL.toString())).doubleValue();
        double pageCount = Math.ceil(count / (double) perpage);

        List<DUserResume> list = dao.findLimit(queryHQL.toString(), perpage * (page - 1), perpage);

        map.put("pageCount", pageCount);
        map.put("list", list);
        return map;
    }

    @Override
    @Transactional(readOnly = true)
    public boolean FlowIsDoing(String flowid) {
        try {
            ProjectInfoFlow flow = (ProjectInfoFlow) dao.findUnique("from ProjectInfoFlow where id='" + flowid + "'");
            if (flow.getState() == EnumTools.PlanFlowState.进行中.ordinal()) {
                return true;
            } else {
                return false;
            }
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    @Override
    @Transactional(readOnly = true)
    public Double getUserCount(String fid, int result) {
        try {
            String hql = "select count(*) from DUserResume where fid='" + fid + "' and result='" + result + "'";
            return ((Long) dao.findUnique(hql)).doubleValue();
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    @SuppressWarnings("unchecked")
    @Override
    @Transactional(readOnly = true)
    public List<DUserResume> getResumes(String fid, int result) throws Exception {
        return dao.find("from DUserResume where fid='" + fid + "' and result='" + result + "'");
    }

    @SuppressWarnings("unchecked")
    @Override
    @Transactional(readOnly = true)
    public List<DUserResume> getResumes(String pid) throws Exception {
        return dao.find("from DUserResume where proid='" + pid + "'");
    }

    @Override
    public String getAddress() {
        try {
            List<BaseConfig> configs = getConfigs("dzmb");
            StringBuilder dz = new StringBuilder();
            for (BaseConfig config : configs) {
                dz.append("<option value='" + config.getCont() + "'>" + config.getCont() + "</option>");
            }
            return dz.toString();
        } catch (Exception e) {
            e.printStackTrace();
            return "";
        }
    }

    @Override
    @Transactional(readOnly = true)
    public ProjectInfo getProjectByFid(String fid) throws Exception {
        return (ProjectInfo) dao.findUnique("from ProjectInfo where id=(select pid from ProjectInfoFlow where id='" + fid + "')");
    }

    @SuppressWarnings("unchecked")
    @Override
    @Transactional(readOnly = true)
    public String optionList(String tcode, String pid) throws Exception {
        try {
            List<BaseData> datas = dao.find("from BaseData where tcode='" + tcode + "' and pid='" + pid + "' order by code");
            StringBuilder str = new StringBuilder();
            for (BaseData data : datas)
                str.append("<option value=\"" + data.getCode() + "\">" + data.getName() + "</option>");
            return str.toString();
        } catch (Exception ex) {
            return null;
        }
    }

    @SuppressWarnings("unchecked")
    @Override
    @Transactional(readOnly = true)
    public List<BaseData> getBaseData(String tcode, String pid) throws Exception {
        try {
            return dao.find("from BaseData where tcode='" + tcode + "' and pid='" + pid + "' order by code");
        } catch (Exception ex) {
            return null;
        }
    }

    @Override
    @Transactional(readOnly = true)
    public BaseEmailConfig getEmailConfig() throws Exception {
        try {
            return (BaseEmailConfig) dao.findUnique("from BaseEmailConfig");
        } catch (Exception e) {
            return null;
        }
    }

    @Override
    @Transactional(readOnly = true)
    public BaseMessageConfig getMessageConfig() throws Exception {
        try {
            return (BaseMessageConfig) dao.findUnique("from BaseMessageConfig");
        } catch (Exception e) {
            return null;
        }
    }

    @Override
    @Transactional(readOnly = true)
    public BaseRegtips getRegtips() throws Exception {
        try {
            return (BaseRegtips) dao.findUnique("from BaseRegtips");
        } catch (Exception e) {
            return null;
        }
    }

    @SuppressWarnings("unchecked")
    @Override
    @Transactional(readOnly = true)
    public String optionListById(String tcode, String pid) throws Exception {
        try {
            List<BaseData> datas = dao.find("from BaseData where tcode='" + tcode + "' and pid='" + pid + "' order by ID");
            StringBuilder str = new StringBuilder();
            for (BaseData data : datas)
                str.append("<option value=\"" + data.getCode() + "\">" + data.getName() + "</option>");
            return str.toString();
        } catch (Exception ex) {
            return null;
        }
    }

    @SuppressWarnings("unchecked")
    @Override
    @Transactional(readOnly = true)
    public Map<String, Object> getJobs(int page, int perpage, String deptname,String jobname) throws Exception {
        Map<String, Object> map = new HashMap<String, Object>();

        StringBuffer queryHQL = new StringBuffer("select d.fullname,j.code,j.name ");
        StringBuffer countHQL = new StringBuffer("select count(1) ");
        StringBuffer condition = new StringBuffer();
        condition.append("from Base_Department d,Base_Job j where d.code=j.deptcode ");
        condition.append("and (d.fullname like '%"+deptname+"%' or d.name like '%"+deptname+"%') and j.name like '%"+jobname+"%'");
        queryHQL.append(condition);
        countHQL.append(condition);
        double count = Double.parseDouble( dao.findUniqueSql(countHQL.toString()).toString());
        double pageCount = Math.ceil(count / (double) perpage);
        List list = dao.findLimitSql(queryHQL.toString(), perpage * (page - 1), perpage);
        map.put("pageCount", pageCount);
        map.put("count", count);
        map.put("list", list);
        return map;

    }

    @Override
    public List<ProjectInfoJobs> getProjectJobs(String projectId) throws Exception {
        String hql=" from ProjectInfoJobs j where j.pid=:projectId   order by j.sortno  asc";
        Map params=new HashMap();
        params.put("projectId",projectId);
        return dao.find(hql,params);
    }

    @Override
    public DoZysx getUserZysx(String pid, String fid, String userid) throws Exception {
        Map params=new HashMap();
        String hql=" from DoZysx z where z.pid=:pid and z.fid=:fid and z.userid=:userid";
        params.put("pid",pid);
        params.put("fid",fid);
        params.put("userid",userid);
        Object result=dao.findUnique(hql,params);
        if (result!=null){
            return  (DoZysx)result;
        }else{
            return null;
        }

    }

    @Override
    public DoMSConfirm getMSUserConfirm(String userid) throws Exception {
        Map params=new HashMap();
        String hql=" from DoMSConfirm c where  c.msuserid=:userid";
        params.put("userid",userid);
        Object result=dao.findUnique(hql,params);
        if (result!=null){
            return  (DoMSConfirm)result;
        }else{
            return null;
        }
    }


    @Override
    public DoMSConfirm getMSUserConfirm(String userid,String type) throws Exception {
        Map params=new HashMap();
        String hql=" from DoMSConfirm c where  c.msuserid=:userid and c.type=:type ";
        params.put("userid",userid);
        params.put("type",type);
        Object result=dao.findUnique(hql,params);
        if (result!=null){
            return  (DoMSConfirm)result;
        }else{
            return null;
        }
    }

    @Override
    public Map queryBySql(String sql) throws Exception {
        return null;
    }

    @Override
    public List<Duserremark> getDuserremark(Duserremark duserremark) throws Exception {
        Map params=new HashMap();
        String hql=" from Duserremark m where 1=1 ";
        if(StringTools.notEmpty(duserremark.getId())){
            hql+=" and m.id=:id ";
            params.put("id",duserremark.getId());
        }
        if(StringTools.notEmpty(duserremark.getPid())){
            hql+=" and m.pid=:pid ";
            params.put("pid",duserremark.getPid());
        }
        if(StringTools.notEmpty(duserremark.getFid())){
            hql+=" and m.fid=:fid ";
            params.put("fid",duserremark.getFid());
        }
        if(StringTools.notEmpty(duserremark.getRid())){
            hql+=" and m.rid=:rid ";
            params.put("rid",duserremark.getRid());
        }

        if(StringTools.notEmpty(duserremark.getCardno())){
            hql+=" and m.cardno=:cardno ";
            params.put("cardno",duserremark.getCardno());
        }
        List result=dao.find(hql,params);
       return  result;
    }

    @Override
    public List groupNomalStateByFid(String fid, String fname) throws Exception {
        String sql="";
        if (fname.equalsIgnoreCase("笔试")){
            sql=" select d.result from dobs_user d where  d.fid='"+fid+"' group by d.result ";
        }else if (fname.equalsIgnoreCase("面试")){
            sql=" select d.result from doms_user d where  d.fid='"+fid+"' group by d.result ";
        }else if(fname.equalsIgnoreCase("实作")){
            sql=" select  from DOOPERATION_USER d where d.fid='"+fid+"' group by d.result ";
        }else if(fname.equalsIgnoreCase("简历筛选")){
                sql=" select u.state from DUSERRESUME u where u.path like '%"+fid+"%' group by u.state ";
        }else if(fname.equalsIgnoreCase("体检")){
                sql=" select u.tjstate from DUSERRESUME u where u.path like '%"+fid+"%' group by u.tjstate ";
        }else if(fname.equalsIgnoreCase("背景调查")||fname.equalsIgnoreCase("政审")){
                sql=" select u.zsstate from DUSERRESUME u where u.path like '%"+fid+"%' group by u.zsstate ";
        }else if(fname.equalsIgnoreCase("资料审核")){
                sql=" select u.zlstate from DUSERRESUME u where u.path like '%"+fid+"%' group by u.zlstate ";
        }else if (fname.equalsIgnoreCase("实习")){
                sql=" select u.sxstate from DUSERRESUME u where u.path like '%"+fid+"%' group by u.sxstate ";
        }else if (fname.equalsIgnoreCase("录用")){
                sql=" select u.bdstate from DUSERRESUME u where u.path like '%"+fid+"%' group by u.bdstate ";
        } else if (fname.equalsIgnoreCase("毕业生推荐")){

        }else if (fname.equalsIgnoreCase("签约")){
            sql=" select u.qystate from DUSERRESUME u where u.path like '%"+fid+"%' group by u.qystate ";
        }
        return dao.executeSQLQuery(sql);
    }

    @Override
    public List groupZZStateByFid(String fid,String fname) throws Exception {
        String sql="";
        if (fname.equalsIgnoreCase("笔试")){
            sql=" select d.result from dobs_user d where  d.fid='"+fid+"' group by d.result ";
        }else if (fname.equalsIgnoreCase("面试")){
            sql=" select d.result from doms_user d where  d.fid='"+fid+"' group by d.result ";
        }else if(fname.equalsIgnoreCase("实作")){
            sql=" select  from DOOPERATION_USER d where d.fid='"+fid+"' group by d.result ";
        }else if(fname.equalsIgnoreCase("简历筛选")){
                sql=" select u.rstate from HR_ZZUSER u where u.path like '%"+fid+"%' group by u.rstate ";

        }else if(fname.equalsIgnoreCase("体检")){
                sql=" select u.tjstate from HR_ZZUSER u where u.path like '%"+fid+"%' group by u.tjstate ";

        }else if(fname.equalsIgnoreCase("背景调查")||fname.equalsIgnoreCase("政审")){
                sql=" select u.zsstate from HR_ZZUSER u where u.path like '%"+fid+"%' group by u.zsstate ";
        }else if(fname.equalsIgnoreCase("资料审核")){

                sql=" select u.zlstate from DUSERRESUME u where u.path like '%"+fid+"%' group by u.zlstate ";
        }else if (fname.equalsIgnoreCase("实习")){
                sql=" select u.sxstate from HR_ZZUSER u where u.path like '%"+fid+"%' group by u.sxstate ";

        }else if (fname.equalsIgnoreCase("录用")){
                sql=" select u.bdstate from HR_ZZUSER u where u.path like '%"+fid+"%' group by u.bdstate ";

        } else if (fname.equalsIgnoreCase("毕业生推荐")){
                sql=" select u.rstate from HR_ZZUSER u where u.path like '%"+fid+"%' group by u.rstate ";

        }else if (fname.equalsIgnoreCase("签约")){
            sql=" select u.qystate from HR_ZZUSER u where u.path like '%"+fid+"%' group by u.qystate ";
        }
        return dao.executeSQLQuery(sql);
    }

    @Override
    @Transactional
    public int getJobMaxID() throws Exception {
        String hql = "from BaseJob where id=(select max(id) from BaseJob)";
        return ((BaseJob) dao.findUnique(hql)).getId();
    }

    @Override
    @Transactional
    public List<Long> getTotalHumanNumber() throws Exception {
        List<Long> thn = new ArrayList<>(4);
        String hql0 = "select count(*) from HRUsers ";
        String hql1 = "select count(*) from UserResume";
        String hql2 = "select count(*)\n" +
                "from DUserResume ur, ProjectInfo pi\n" +
                "where ur.fqstate='未放弃' and ur.proid = pi.id and (pi.recruittype = " + EnumTools.RecruitType.中专校园招聘.ordinal() +
                " or pi.recruittype = " + EnumTools.RecruitType.高校校园招聘.ordinal() +" )";
        String hql3 = "select count(*)\n" +
                "from DUserResume ur, ProjectInfo pi\n" +
                "where ur.fqstate='未放弃' and ur.proid = pi.id and pi.recruittype = " + EnumTools.RecruitType.社会招聘.ordinal();
        long tmp = (long)dao.findUnique(hql0);
        thn.add(tmp);
        tmp = (long) dao.findUnique(hql1);
        thn.add(tmp);
        tmp = (long) dao.findUnique(hql2);
        thn.add(tmp);
        tmp = (long) dao.findUnique(hql3);
        thn.add(tmp);
        return thn;
    }

    @Override
    @Transactional
    @SuppressWarnings("unchecked")
    public List getAnnualHumanNumber() throws Exception {
        String getYearsSql = "select distinct to_char(ur.adddate, 'yyyy') as yr," +
                "bdstate ," +
        "COUNT(*) OVER (partition by bdstate,to_char(ur.adddate, 'yyyy')) as cnt " +
        "from DUserResume ur " +
        "order by yr asc";
        return (List) dao.findSql(getYearsSql);
    }

    @Override
    @Transactional
    @SuppressWarnings("unchecked")
    public List<ProjectInfoFlow> getFlowsByPid(String pid) throws Exception {
        Map<String, Object> param = new HashMap<>();
        param.put("pid", pid);
        String getflowHQL = "from ProjectInfoFlow flow\n" +
                "where flow.pid=:pid\n" +
                "order by flow.forder";
        return (List<ProjectInfoFlow>) dao.find(getflowHQL, param);
    }

    @Override
    @Transactional
    @SuppressWarnings("unchecked")
    public List passStateStatistic(String keyword, String year) throws Exception{
        String sql = "select flow.forder \"forder\", flow.fname \"fname\", flow.nickname \"nickname\",\n" +
                " ur.zlstate \"zlstate\", ur.zsstate \"zsstate\", ur.bdstate \"bdstate\", ur.state \"state\",\n" +
                " ur.tjstate \"tjstate\", msur.result \"msresult\", bsur.result \"bsresult\"\n" +
                "from project_info_flow flow, project_info pro, duserresume ur\n" +
                "left join doms_user msur on msur.fid=ur.fid and msur.userid=ur.id\n" +
                "left join dobs_user bsur on bsur.fid=ur.fid and bsur.userid=ur.id\n" +
                "where flow.state = 1 and ur.proid=flow.pid  and to_char(pro.startdate, 'yyyy')=:year \n";
        String orderby ="order by flow.forder ";
        String sufix = " and pro.pname=:keyword\n";
        Map<String, Object> params = new HashMap<>();
        params.put("year", year);
        if (keyword != null && !keyword.isEmpty()) {
            sql += sufix;
            params.put("keyword", keyword);
        }
        sql += orderby;
        return dao.executeSQLQuery(sql, params);

    }

    @Override
    @Transactional
    public List distinctProjectYear() throws Exception{
        String sql = "select distinct to_char(startdate, 'yyyy')\n" +
                "from project_info";
        return dao.executeSQLQuery(sql);
    }


}
